﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sinacor.Infra.Service.Security.Authorization;
using System.Data.Common;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Common;
using System.Data;

namespace Sinacor.Servico.Bovespa.Cliente.ResourceAccess
{
    /// <summary>
    /// Acesso a dados de Tipo Conta
    /// </summary>
    public class TipoConta : BaseResourceAccess
    {
        #region Construtor
        /// <summary>
        /// Construtor padrão
        /// </summary>
        public TipoConta()
            : base(AuthorizationContext.Current.CompanyId)
        {

        }

        #endregion

        #region Métodos
        /// <summary>
        /// Altera o Tipo de Conta
        /// </summary>
        /// <param name="request"></param>
        public void AlterarTipoConta(BusinessEntity.TipoConta request) 
        {
            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append(" UPDATE TBOCARPRO SET TP_CLIENTE = :TipoCliente, ");
            sqlCommand.Append(" IN_ESPECIALISTA = :Especialista ");
            sqlCommand.Append(" WHERE CD_CLIENTE = :CodigoCliente ");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sqlCommand.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, "TipoCliente", System.Data.DbType.String, EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.TipoCliente.Value));
                this.DataBase.AddInParameter(dbCommand, "Especialista", System.Data.DbType.String, (request.Especialista.Value)?"S":"N");
                this.DataBase.AddInParameter(dbCommand, "CodigoCliente", System.Data.DbType.Int32, request.CodigoCliente.Value);
                this.DataBase.ExecuteNonQuery(dbCommand);
            }
        }

        /// <summary>
        /// Consulta os Tipos de Contas
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<BusinessEntity.TipoConta> ConsultarTiposConta(BusinessEntity.Parameter.FiltroTipoConta request) 
        {
            List<BusinessEntity.TipoConta> retorno = null;
            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append(" SELECT ");
            sqlCommand.Append(" A.CD_CLIENTE AS Codigo,  ");
            sqlCommand.Append(" B.NM_APELIDO AS Apelido,  ");
            sqlCommand.Append(" A.TP_CLIENTE AS TipoCliente,  ");
            sqlCommand.Append(" A.IN_ESPECIALISTA AS Especialista  ");
            sqlCommand.Append(" FROM TBOCARPRO A ");
            sqlCommand.Append(" INNER JOIN TSCCLIBOL B ON A.CD_CLIENTE = B.CD_CLIENTE WHERE 1=1 ");
            
            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(" "))
            {
                if(request.CodigoCliente != null)
                {
                    sqlCommand.Append(" AND A.CD_CLIENTE = :CodigoCliente ");
                    this.DataBase.AddInParameter(dbCommand, "CodigoCliente", System.Data.DbType.Int32, request.CodigoCliente.Value);
                }
                    

                if(!string.IsNullOrEmpty(request.ApelidoCliente))
                {
                    sqlCommand.Append(" AND B.NM_APELIDO LIKE '%'||:ApelidoCliente||'%' ");
                    this.DataBase.AddInParameter(dbCommand, "ApelidoCliente", System.Data.DbType.String, request.ApelidoCliente);   
                }

                if(request.TipoCliente != null)
                {
                    sqlCommand.Append(" AND A.TP_CLIENTE = :TipoCliente ");
                    this.DataBase.AddInParameter(dbCommand, "TipoCliente", System.Data.DbType.String, EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.TipoCliente.Value));   
                }

                if(request.Especialista != null)
                {
                    sqlCommand.Append(" AND A.IN_ESPECIALISTA = :Especialista ");
                    this.DataBase.AddInParameter(dbCommand, "Especialista", System.Data.DbType.String, (request.Especialista.Value) ? "S" : "N");               
                }
                sqlCommand.Append(" ORDER BY B.NM_APELIDO ");

                dbCommand.CommandText = sqlCommand.ToString();

                retorno = this.FindAll<BusinessEntity.TipoConta>(dbCommand, new ConstructEntity<Sinacor.Servico.Bovespa.Cliente.BusinessEntity.TipoConta>(ConstrutorConsultarTiposConta));
            }

            return retorno;


        }

        private BusinessEntity.TipoConta ConstrutorConsultarTiposConta(IDataReader reader) 
        {
            BusinessEntity.TipoConta tipoConta = new Sinacor.Servico.Bovespa.Cliente.BusinessEntity.TipoConta();
            tipoConta.CodigoCliente = reader.GetValue<int?>("Codigo");
            tipoConta.ApelidoCliente = reader.GetValue<string>("Apelido");
            tipoConta.Especialista = (reader.GetValue<string>("Especialista") == "S") ? true : false;
            tipoConta.TipoCliente = EnumConverter.Parse<BusinessEntity.Enum.TipoCliente?>(reader.GetValue<string>("TipoCliente"));

            return tipoConta;
        }

        /// <summary>
        /// Exclui um Tipo de Conta
        /// </summary>
        /// <param name="request"></param>
        public void ExcluirTipoConta(BusinessEntity.Parameter.FiltroCodigoCliente request) 
        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append(" DELETE FROM TBOCARPRO WHERE CD_CLIENTE = :CodigoCliente ");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sqlCommand.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, "CodigoCliente", System.Data.DbType.Int32, request.Codigo);
                this.DataBase.ExecuteNonQuery(dbCommand);
            }
        }

        /// <summary>
        /// Inclui um Tipo de Conta
        /// </summary>
        /// <param name="request"></param>
        public void IncluirTipoConta(BusinessEntity.TipoConta request) 
        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append(" INSERT INTO TBOCARPRO ");
            sqlCommand.Append(" (CD_CLIENTE, ");
            sqlCommand.Append(" TP_CLIENTE, ");
            sqlCommand.Append(" IN_ESPECIALISTA) ");
            sqlCommand.Append(" VALUES ");
            sqlCommand.Append(" (:CodigoCliente, ");
            sqlCommand.Append(" :TipoCliente, ");
            sqlCommand.Append(" :Especialista) ");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sqlCommand.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, "CodigoCliente", System.Data.DbType.Int32, request.CodigoCliente.Value);
                if(request.Especialista != null)
                    this.DataBase.AddInParameter(dbCommand, "Especialista", System.Data.DbType.String, (request.Especialista.Value) ? "S" : "N");
                else
                    this.DataBase.AddInParameter(dbCommand, "Especialista", System.Data.DbType.String, "N");

                this.DataBase.AddInParameter(dbCommand, "TipoCliente", System.Data.DbType.String, EnumConverter.ToString<BusinessEntity.Enum.TipoCliente>(request.TipoCliente.Value));


                this.DataBase.ExecuteNonQuery(dbCommand);
            }

        }

        #endregion
    }
}
